package shopNet;
import java.sql.*;
import java.util.Vector;

import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;
public class DBUtil {
	static final String URL = "jdbc:mysql://localhost:3306/shop?useSSL=false";
	static final String USER = "root";
	static final String PWD = "123456";
	static Connection con;
	
	public static void linkDB() {
		try {
			con = DriverManager.getConnection(URL, USER, PWD);
			System.out.println(con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static boolean queryLogin(String username,String password) {
		PreparedStatement prstm = null;
		ResultSet rs = null;
		boolean succ = false;
		String sql = "select * from users where username=? and password=?";
		try {
			prstm = con.prepareStatement(sql);
			prstm.setString(1, username);
			prstm.setString(2, password);
			rs = prstm.executeQuery();
			if(rs.next()) {
				succ = true;
			}else {
				succ = false;
			}
			rs.close();
			prstm.close();
			return succ;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return succ;
	}
	public static String queryAllGoods() {
		String response = "";
		PreparedStatement prstm = null;
		ResultSet rs = null;
		String sql = "select * from goods";
		try {
			prstm = con.prepareStatement(sql);
			rs = prstm.executeQuery();
			while(rs.next()) {
				int i = rs.getInt(1);
				String goodsname = rs.getString(2);
				int number = rs.getInt(3);
				float price = rs.getFloat(4);
				response=response+i+":"+goodsname+":"+number+":"+price+"\n";
			}
			response = response+"end\n";
			rs.close();
			prstm.close();
			return response;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return response;
	}
	public static String queryPartGoods(String goodsName) {
		String response = "";
		PreparedStatement prstm;
		ResultSet rs;
		String sql = "select * from goods where goodsname=?";
		try {
			prstm = con.prepareStatement(sql);
			prstm.setString(1, goodsName);
			rs = prstm.executeQuery();
			while(rs.next()) {
				int i = rs.getInt(1);
				String goodsname = rs.getString(2);
				int number = rs.getInt(3);
				float price = rs.getFloat(4);
				response=response+i+":"+goodsname+":"+number+":"+price+"\n";
			}
			response = response+"end\n";
			rs.close();
			prstm.close();
			return response;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return response;
	}
}
